﻿--select count(CXCRMID), AgentID from tblCXCRMCallStatus Where Convert(varchar(8), CreatedDate, 112) = Convert(varchar(8), GetDate(), 112) 
--Group By AgentID

--select top 10 * from tblCXCRMAgentLeads Order By ModifiedDate DESC

--Number of Lead by Agents
--SELECT     L.AgentID, COUNT(L.CustomerID)
--FROM tblCXCRMAgentLeads AS L 
----INNER JOIN tblCXCRMCallStatus AS CS ON L.CarID = CS.CarId AND L.CustomerID = CS.CustomerId
--INNER JOIN tblCarDetailTH AS CD ON L.CarID = CD.CarId AND L.CustomerID = CD.RefCEUserId
--WHERE (CONVERT(varchar(8), CD.CreatedDate, 112) Between '20120601' And '20120618') And L.CallType = 101
--GROUP BY L.AgentID

--select AgentID, COUNT(CustomerID) from tblCXCRMCallStatus
--Where LEN(Remark) = 0
--Group By AgentID, Convert(varchar(6), ModifiedDate, 112)
--Having Convert(varchar(6), ModifiedDate, 112) = '201205'

--select AgentID, COUNT(CustomerID) from tblCXCRMCallStatusLog
--Where LEN(Remark) = 0
--Group By AgentID, Convert(varchar(6), ModifiedDate, 112)
--Having Convert(varchar(6), ModifiedDate, 112) = '201205'

--select AgentID, COUNT(CustomerID) from tblCXCRMAgentLeads
--Group By AgentID, Convert(varchar(6), CreatedDate, 112)
--Having Convert(varchar(6), CreatedDate, 112) = '201205'

--select top 10 * from tblLoginHistory Where UsrTypeId = 200 Order By LoginHisId DESC

--select CEID, MAX(Convert(varchar(8), StartTime, 112)), COUNT(LoginHisId) from tblLoginHistory
--Group By CEID, UsrTypeId, Convert(varchar(6), StartTime, 112)
--Having Convert(varchar(6), StartTime, 112) = '201205' And UsrTypeId = 200

--select CEID, MAX(Convert(varchar(8), StartTime, 112)), COUNT(LoginHisId) from tblLoginHistory
--Group By CEID, UsrTypeId, Convert(varchar(8), StartTime, 112)
--Having UsrTypeId = 200 And CEID = 547 
--And Convert(varchar(8), StartTime, 112) Between '20120501' And '20120531'

--select COUNT(RefCEUserID) from tblCarDetailTH 
--WHERE Convert(varchar(8), CreatedDate, 112) Between '20120601' And '20120618'
--And (RefCEUserId IS NOT NULL And RefCEUserId > 0)

--select COUNT(RefCEUserID) from tblContactUser 
--WHERE Convert(varchar(8), CreatedDate, 112) Between '20120601' And '20120618'

--select * from tblCEUser Where UsrTypeId = 200

--Open Symmetric Key CXUserSym
--Decryption By Certificate CXUserCert

----select top 10 CEID, UsrTypeId, Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Email from tblCEUser Order By ModifiedDate DESC

--Select CEID, UsrTypeId, Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Email, IsReject,
--Convert(varchar(50), DECRYPTBYKEY(EInfo2)) AuthCode, Ninfo4, CreatedDate  from tblCEUser 
----Where  Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Like'%test%' And UsrTypeId = 400
--Where UsrTypeId = 200
--Close Symmetric Key CXUserSym

--Update tblCEUser SET Ninfo4 = 200 Where CEID = 535
--535		cx.tester188@comparexpress.com
--547		user1@comparexpress.com
--840		kyawthant.phyo@teledirect.com.sg
--844		arthit.a@comparexpress.com
--846		paemika.a@comparexpress.com
--1394	kandasvel@gmail.com
--1499	saejung.wanlapa@gluaygluay.com


--RefCEUserId		Email								AuthCode		UserRole	SurName	FirstName	OfficeNumber	SMPTKey
--535				cx.tester188@comparexpress.com		CXthai188		1			CX		TESTER		x	
--547				nittaya.juntra@gluaygluay.com 		JUntra5478		0			Juntra	Nittaya		x	
--547				nittaya.juntra@gluaygluay.com 		JUntra5478		0			Juntra	Nittaya		x	
--840				kyawthant.phyo@teledirect.com.sg	PAss5432		1			PHYO	CX ADMIN		
--844				arthit.a@comparexpress.com			CXarthit5288	1			Arthit	Amornkul		
--846				paemika.a@comparexpress.com			BYEbye2222		1			(Aoy)	Paemika	Amarit		
--1394				kandasvel@gmail.com					SAkthi12		1			Sakthivel	K		
--1499				saejung.wanlapa@gluaygluay.com		WWann8887879	0			Saejung	Wanlapa	8	
--5571				vichada.c@comparexpress.com			GGcashier12		NULL		Cashier	GG	0

--RefCEUserId		Email								AuthCode		UserRole	SurName			FirstName	OfficeNumber			SMPTKey
--535				admin@gluaygluay.com				CXthai188		200			Admin			GG			0-2268-8222				Admin
--547				nittaya.juntra@gluaygluay.com 		JUntra5478		100			นิตยา							0-2268-8222 ต่อ 2229		Broker1
--840				kyawthant.phyo@teledirect.com.sg	PAss5432		200			Phyo			Admin		0-2268-8222				Admin
--844				arthit.a@comparexpress.com			CXarthit5288	300			Arthit			Amornkul	0-2268-8222				Admin
--846				paemika.a@comparexpress.com			BYEbye2222		200			(Aoy) Paemika	Amarit		0-2268-8222				Admin
--1394				kandasvel@gmail.com					SAkthi12		200			Sakthivel		Admin		0-2268-8222				Admin
--1499				saejung.wanlapa@gluaygluay.com		WWann8887879	100			วัลภา							0-2268-8222 ต่อ 2228		Broker2
--5571				vichada.c@comparexpress.com			GGcashier12		300			Cashier	GG					0-2268-8222				Cashier

--Broker1    นิตยา จันทรา         broker1@gluaygluay.com  #2229
--Broker2    วัลภา แซ่จึง           broker2@gluaygluay.com  #2228
--Broker3    สุริยา นันทจักร์       broker3@gluaygluay.com  #2227

	--547	broker1@gluaygluay.com
	--18808	broker3@gluaygluay.com
	--8543	broker5@gluaygluay.com
	--18813	broker6@gluaygluay.com
	--8546	broker7@gluaygluay.com
	--18818	broker8@comparexpress.co.th

--select l.* from tblCXCRMAgentLeads l INNER JOIN tblCEUsrProfile p ON
--l.CustomerID = p.RefCEUserID 
-- Where l.AgentID = 18818 And p.NInfo7 = 0
--And l.[Status] = 1 Order By l.ModifiedDate DESC

--select Count(AgentLeadID), AgentID, [Status]
--From tblCXCRMAgentLeads
--Where ([Status] = 1) And Convert(varchar(8), CreatedDate, 112) > '20121101'
--And (CallType = 100 OR CallType = 101)
--Group By AgentID, [Status]

Open Symmetric Key CXUserSym
Decryption By Certificate CXUserCert

Select c.RefCEUserId, u.Info1, Convert(varchar(50), DECRYPTBYKEY(u.EInfo1)) Email, 
Convert(varchar(50), DECRYPTBYKEY(u.EInfo2)) AuthCode, 
u.Ninfo4 As UserRole, 
u.NInfo5 UserCampaign,
u.NInfo6 IsRenewal,
Convert(varchar(50), DECRYPTBYKEY(p.EInfo1)) AS FirstName,
Convert(varchar(50), DECRYPTBYKEY(p.EInfo2)) AS SurName, 
p.Info1,
p.Info2,
p.Info3,
p.Gender,
Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) AS OfficeNumber,
Convert(nvarchar(50), DECRYPTBYKEY(c.EInfo4)) AS HomeNumber,
--'02-268-8222 press 4 ext. ' + RIGHT(Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)), 4),
--Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) AS OfficeNumber
--02-268-8222 press 4 ext. 2234
--02-268-8222 กด 4 ต่อ 2234
c.Info1 AS SMPTKey, 
u.Status,
c.CreatedDate
from tblCEContact c
INNER JOIN tblCEUser u ON c.RefCeUserID = u.CEID
INNER JOIN tblCEUsrProfile p ON p.RefCEUserId = u.CEID
Where u.UsrTypeID = 200 And u.[Status] = 1
--And Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) LIKE '%กด%'

--Select Convert(varchar(50), DECRYPTBYKEY(EInfo5)), Email from tblCEContact
--Where Convert(varchar(50), DECRYPTBYKEY(EInfo5)) LIKE '%กด%'

--Update tblCEUsrProfile
--SET 
--EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), 'รัสรินทร์'),
--EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), 'ปรีชาจิระพัฒน์')
--Where RefCEUserID = 18813

--Update tblCEUser SET CreatedDate = GetDate(), ModifiedDate = GetDate(), rowguid=newid() Where  CEID = 55800
--Update tblCEUsrProfile SET CreatedDate = GetDate(), ModifiedDate = GetDate() Where  RefCEUserID = 55800
--Update tblCEContact SET CreatedDate = GetDate(), ModifiedDate = GetDate() Where  RefCEUserID = 55800

--select * from tblCEUser Where UsrTypeID = 200

--Update tblCEUser SET Info1 = 'ONJ'  Where CEID = 45914
--Where UsrTypeID = 200
--AND CEID IN (45918, 45920, 45922, 45923)
--Where CEID IN (41371, 45914, 45917, 45918, 45920, 45922, 45923)
--ONLINE LEAD SENIOR = ONS
--ONLINE LEAD JUNIOR = ONJ
--NO ONLINE LEAD	 = NOL

--Update tblCEUser SET Status = 1 Where CEID = 41371
--Where CEID IN (41371, 45918, 45920, 45922, 45923)

--Update tblCEUser SET Info1 = 'ONJ' Where CEID = 45917
--Update tblCEUser SET NInfo6 = 0 Where CEID IN (45914,
--45917,
--45918,
--45920,
--45922,
--45923)
--Declare @targetDate DateTime
--SET @targetDate = '2013-03-29 12:00:00.000'

--Declare @curDate DateTime
--SET @curDate = GetDate() 

--IF @curDate > @targetDate
--BEGIN
--	Select 'Greater'
--END
--ELSE
--BEGIN
--	Select 'Not Greater'
--END

--select Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) AS OfficeNumber, c.* from tblCEContact c
--INNER JOIN tblCEUser u ON c.RefCeUserID = u.CEID
--Where u.UsrTypeID = 200 

--Select ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), CAST('02-268-8222' + ' กด 4 ต่อ ' + Right(c.OfficePhone, 4) as nvarchar))
--FROM
--tblCEContact c
--INNER JOIN tblCEUser u ON c.RefCeUserID = u.CEID
--Where u.UsrTypeID = 200 And c.OfficePhone Like '%ต่อ%'

--Update c 
--SET c.EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'), CAST('02-268-8222' + ' กด 4 ต่อ ' + Right(c.OfficePhone, 4) as varchar))
--FROM
--tblCEContact c
--INNER JOIN tblCEUser u ON c.RefCeUserID = u.CEID
--Where u.UsrTypeID = 200 And c.OfficePhone Like '%ต่อ%'

--AND Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) LIKE '%ต่อ%'

--select * from tblUserStatus Where UsrTypeID = 200 Order By ModifiedDate DESC

--Update tblCEUsrProfile SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'Locharoenrat'),
-- EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'Nopawan ')
--Where RefCEUserId = 24292

--Update tblCEUsrProfile SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'Locharoenrat'),
-- EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'Nopawan')
--Where RefCEUserId = 547

--Update tblCEUser SET EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'offline@gluaygluay.com'),
-- EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'OLbr1046 '), NInfo5 = 1001
--Where CEID = 24292




--Select Convert(varchar(50), DECRYPTBYKEY(u.EInfo1)) Email, 
--Convert(varchar(50), DECRYPTBYKEY(p.EInfo1)) AS FirstName,
--Convert(varchar(50), DECRYPTBYKEY(p.EInfo2)) AS SurName, 
--p.Gender,
--Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) AS OfficeNumber
--from tblCEContact c
--INNER JOIN tblCEUser u ON c.RefCeUserID = u.CEID
--INNER JOIN tblCEUsrProfile p ON p.RefCEUserId = u.CEID
--Where u.UsrTypeID = 200 And Convert(varchar(50), DECRYPTBYKEY(u.EInfo1)) Like 'broker_@%'
--Order By Convert(varchar(50), DECRYPTBYKEY(u.EInfo1))
--select top 10 * from tblCEUser Where UsrTypeID = 200

--Update tblCEUser SET NInfo5 = 400 Where UsrTypeID = 200

--Update tblCEUser SET NInfo4 = 201 Where CEID = 30875

--Update tblCEUser SET NInfo5 = 1001 Where CEID IN (24292, 24293, 1499)

Close Symmetric Key CXUserSym

--Update tblCEUsrProfile SET Gender = 'F' Where RefCEUserId = 535
--Update tblCEUsrProfile SET Gender = 'F' Where RefCEUserId = 547
--Update tblCEUsrProfile SET Gender = 'F' Where RefCEUserId = 1499
--Update tblCEUsrProfile SET Gender = 'F' Where RefCEUserId = 5571


--Select CEID, UsrTypeId, Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Email, 
--Convert(varchar(50), DECRYPTBYKEY(EInfo2)) AuthCode, Ninfo4, CreatedDate  from tblCEUser 
--Where  Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Like'%kyawthant%' And UsrTypeId = 400
--And UsrTypeId = 400

--Close Symmetric Key CXUserSym

--select * from tblCEContact Where RefCEUserId = 535
--select * from tblCEContact Where RefCEUserId = 54700000000

/*****Updating User Type and Email****/
--Select CEID, UsrTypeId, Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Email, 
--Convert(varchar(50), DECRYPTBYKEY(EInfo2)) AuthCode, Ninfo4, CreatedDate  from tblCEUser 
----Where  Convert(varchar(50), DECRYPTBYKEY(EInfo1)) Like'%test%' And UsrTypeId = 400
--Where UsrTypeId = 200
--update tblCEUser SET Ninfo4 = 300 Where CEID = 6807
--Update tblCEUser SET UsrTypeId = 200, EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'broker1@gluaygluay.com') Where CEID = 547
--Update tblCEUser SET UsrTypeId = 200, EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'broker2@gluaygluay.com') Where CEID = 1499
--Update tblCEUser SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'BRok1234') Where CEID = 1392
--Update tblCEUser SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'BRok2234') Where CEID = 1423

/*****Updating Name****/
--Select p.RefCEUserId, Convert(varchar(50), DECRYPTBYKEY(p.EInfo2)) AS SurName, 
--Convert(varchar(50), DECRYPTBYKEY(p.EInfo1)) AS FirstName from tblCEUsrProfile p 
--INNER JOIN tblCEUser u ON p.RefCeUserID = u.CEID
--Where u.UsrTypeID = 200

--Broker1    นิตยา จันทรา         broker1@gluaygluay.com  #2229
--Broker2    วัลภา แซ่จึง           broker2@gluaygluay.com  #2228
--Broker3    สุริยา นันทจักร์       broker3@gluaygluay.com  #2227

--Update tblCEUsrProfile SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'จันทรา'),
-- EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'นิตยา')
--from tblCEUsrProfile Where RefCEUserId = 547

--Update tblCEUsrProfile SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'แซ่จึง'),
-- EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'วัลภา')
--from tblCEUsrProfile Where RefCEUserId = 1499

--Update tblCEUsrProfile SET EInfo2 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'นันทจักร์'),
-- EInfo1 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'สุริยา')
--from tblCEUsrProfile Where RefCEUserId = 1423

/*****Updating User Role****/
--Update tblCEUser SET Ninfo4 = 100 Where CEID = 1392
--Update tblCEUser SET Ninfo4 = 300 Where CEID = 844

/*****Updating Phone No****/
--Select c.RefCEUserId, Convert(varchar(50), DECRYPTBYKEY(c.EInfo5)) AS OfficeNumber from tblCEContact c
--INNER JOIN tblCEUser u ON c.RefCeUserID = u.CEID
--Where u.UsrTypeID = 200
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222 ต่อ 2228') Where RefCEUserID = 1499
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222 ต่อ 2229') Where RefCEUserID = 547
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222') Where RefCEUserID = 535
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222') Where RefCEUserID = 840
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222') Where RefCEUserID = 844
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222') Where RefCEUserID = 846
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222') Where RefCEUserID = 1394
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222') Where RefCEUserID = 5571
--Update tblCEContact SET EInfo5 = ENCRYPTBYKEY(KEY_GUID(N'CXUserSym'),'0-2268-8222 ต่อ 2227') Where RefCEUserID = 1423


/***Updating SMTP Setting***/
--Update tblCEContact SET Info1 = 'Admin' Where RefCEUserId = 535
--Update tblCEContact SET Info1 = 'Cashier' Where RefCEUserId = 5571
--Update tblCEContact SET Info1 = 'Admin' Where RefCEUserId = 840
--Update tblCEContact SET Info1 = 'Admin' Where RefCEUserId = 844
--Update tblCEContact SET Info1 = 'Admin' Where RefCEUserId = 846
--Update tblCEContact SET Info1 = 'Admin' Where RefCEUserId = 1394
--Update tblCEContact SET Info1 = 'Broker2' Where RefCEUserId = 547
--Update tblCEContact SET Info1 = 'Broker1' Where RefCEUserId = 1499



--select * from tblCXBrokerPremiumQuotationEmail



--select * from tblCXBrokerPremium
--select * from tblNonGGCarMoreDetails
--select * from tblNonGGCarMoreDetails_Log
--select * from tblNonGGTransaction
--select * from tblNonGGTransaction_Log
--select * from tblPayments
--select * from tblPayments_Log
--select * from tblNonGGCarDriver
--select * from tblNonGGCarFileUploadDetails
--select * from tblNonGGCarFileUploadDetails_Log

--truncate table tblCXBrokerPremium
--truncate table tblNonGGCarMoreDetails
--truncate table tblNonGGCarMoreDetails_Log
--truncate table tblNonGGTransaction
--truncate table tblNonGGTransaction_Log
--truncate table tblPayments
--truncate table tblPayments_Log
--truncate table tblNonGGCarDriver
--truncate table tblNonGGCarFileUploadDetails
--truncate table tblNonGGCarFileUploadDetails_Log

--select * from tblNonGGCarMoreDetails

--select * from tblCXBrokerPremium Where CECarID = 9026
--select * from tblNonGGCarMoreDetails Where CECarid = 9026

--delete from tblCXBrokerPremium Where CECarId = 9026
--delete from tblNonGGCarMoreDetails Where CECarId = 9026
--delete from tblNonGGTransaction Where CECarID = 9026
--delete from tblPayments Where CECarID = 9026
--select * from tblNonGGTransaction Where CECarID = 9026